from functools import wraps

import uuid
from flask import Flask, request, jsonify, send_file

from flask_mail import Mail, Message
import random
import string
import os
from datetime import datetime


from werkzeug.security import generate_password_hash, check_password_hash
from werkzeug.utils import secure_filename
from 用户操作.生成图片函数 import transform
import pymysql
from pymysql.cursors import DictCursor


app = Flask(__name__)

# 数据库配置
# app.config['DB_CONFIG'] = {
#     'host': '127.0.0.1',
#     'port': 3306,
#     'user': 'root',
#     'password': '88888888',
#     'database': 'soundsight',
#     'charset': 'utf8mb4',
#     'cursorclass': DictCursor,
#     'autocommit': True
# }

# 邮箱配置
# app.config['MAIL_SERVER'] =
# app.config['MAIL_PORT']=
# app.config['MAIL_USE_TLS'] =
# app.config['MAIL_USERNAME'] =
# app.config['MAIL_PASSWORD'] =
# app.config['MAIL_DEFAULT_SENDER'] =

app.config['MAX_IMAGE_SIZE'] = 2 * 1024 * 1024  # 2MB
app.config['MAX_PROFILE_IMAGE_SIZE'] = 512 * 512  # 256kB
app.config['MAX_AUDIO_SIZE'] = 5 * 1024 * 1024  # 5MB



# 初始化数据库连接
def get_db_connection():
    return pymysql.connect(**app.config['DB_CONFIG'])

# 注册一个teardown_appcontext处理器来关闭数据库连接
@app.teardown_appcontext
def close_db_connection(exception):
    if hasattr(app, 'db_conn'):
        app.db_conn.close()


# 配置邮件


# 允许的图片文件类型


# 允许的音频文件类型

mail = Mail(app)

app.config['UPLOAD_FOLDER'] = 'uploads'  # 音频临时保存目录
app.config['RESULT_FOLDER'] = 'result_images'  # 图片输出目录
os.makedirs(app.config['UPLOAD_FOLDER'], exist_ok=True)
os.makedirs(app.config['RESULT_FOLDER'], exist_ok=True)

def generate_verification_code(length=6):
    #生成一个6位数的验证码
    letters_and_digits = string.ascii_letters + string.digits
    return ''.join(random.choice(letters_and_digits) for i in range(length))



@app.route('/register', methods=['POST'])#注册
def register():#注册
    data = request.json#获取请求的json数据
    username = data.get('username')#获取用户名
    password = data.get('password')#获取密码
    email = data.get('email')#获取邮箱

    #如果用户名短于6位或者用户名中含有@或者密码短于6位或者邮箱中不含有@或者邮箱中不含有.或者邮箱中@的位置在.的位置之后
    if len(username) < 6 or '@' in username or len(password) < 6 or '@' not in email or '.' not in email or email.index('@') > email.index('.'):
        return jsonify({"message": "用户名或密码或邮箱格式不正确"}), 400

    if not username or not password or not email:#如果用户名、密码或邮箱为空
        return jsonify({"message": "缺少数据"}), 400

    try:
        # 获取数据库连接
        conn = get_db_connection()
        cursor = conn.cursor()

        # 检查用户名或邮箱是否已存在
        cursor.execute(
            "SELECT * FROM 用户表 WHERE 用户名称 = %s OR 用户邮箱 = %s",
            (username, email)
        )
        existing_user = cursor.fetchone()

        if existing_user:
            return jsonify({"message": "用户名或邮箱已存在,如未激活请激活后使用"}), 400

        # 插入新用户
        verification_code = generate_verification_code()  # 生成验证码
        hashed_password = generate_password_hash(password)
        cursor.execute(
            "INSERT INTO 用户表 (用户名称,用户密码,用户邮箱,验证码,用户状态) VALUES (%s,%s,%s,%s,%s)",
            (username, hashed_password, email,verification_code,"create")  # 注意：实际应用中密码应该加密存储
        )

        # 提交事务（虽然设置了autocommit=True，但显式提交是个好习惯）
        conn.commit()

        msg = Message("SoundSight账号验证码", recipients=[email])  # 创建邮件
        msg.body = f'您正在注册SoundSight账号，您的验证码为 {verification_code}如果不是本人操作，请忽略本次邮件,如未激活,账号将会在24小时后自动删除'  # 设置邮件内容
        mail.send(msg)  # 发送邮件

        return jsonify(
            {"message": "用户创建成功，请检查邮箱使用验证码进行激活"}), 201


    except pymysql.Error as e:
        # 发生错误时回滚
        conn.rollback()
        return jsonify({"message": f"数据库错误: {str(e)}"}), 500

    finally:
        # 确保关闭游标和连接
        if 'cursor' in locals():
            cursor.close()
        if 'conn' in locals():
            conn.close()


@app.route('/verify', methods=['POST'])
def verify():
    data = request.json
    username = data.get('username')
    password = data.get('password')
    email = data.get('email')
    code = data.get('code')

    if not username or not code or not password or not email:
        return jsonify({"message": "请求错误"}), 400

    try:
        # 获取数据库连接
        conn = get_db_connection()
        cursor = conn.cursor()

        # 查询用户信息
        cursor.execute(
            "SELECT 用户密码, 用户邮箱, 验证码, 用户状态, 尝试次数 FROM 用户表 WHERE 用户名称 = %s",
            (username,)
        )
        user_data = cursor.fetchone()
        # print("查询结果:", user_data)  # 先直接打印整个结果
        # print("结果类型:", type(user_data))  # 查看返回的数据类型

        if not user_data:
            return jsonify({"message": "账号没有注册"}), 400

        try:
            db_password = user_data["用户密码"]
            db_email = user_data["用户邮箱"]
            db_code = user_data["验证码"]
            db_state = user_data["用户状态"]
            db_times = user_data["尝试次数"]
        except (ValueError, TypeError) as e:
            # 处理解包错误
            return jsonify({"message": "数据解析错误"}), 500

        # 检查用户状态
        if db_state != "create":
            return jsonify({"message": "用户已激活"}), 400


        if int(db_times) >= 5:
            return jsonify({"message": "尝试次数过多,账号将在24小时后自动删除"}), 400

        # 验证密码（假设密码已加密存储）
        if not(check_password_hash(db_password, password)):
            # 增加尝试次数
            cursor.execute(
                "UPDATE 用户表 SET 尝试次数 = 尝试次数 + 1 WHERE 用户名称 = %s",
                (username,)
            )
            conn.commit()
            return jsonify({"message": "密码错误"}), 400

        # 验证邮箱
        if email != db_email:
            # 增加尝试次数
            cursor.execute(
                "UPDATE 用户表 SET 尝试次数 = 尝试次数 + 1 WHERE 用户名称 = %s",
                (username,)
            )
            conn.commit()
            return jsonify({"message": "邮箱错误"}), 400



        # 验证验证码
        if db_code != code:
            # 增加尝试次数
            cursor.execute(
                "UPDATE 用户表 SET 尝试次数 = 尝试次数 + 1 WHERE 用户名称 = %s",
                (username,)
            )
            conn.commit()
            return jsonify({"message": "验证码错误"}), 400

        # 验证成功，更新用户状态
        cursor.execute(
            "UPDATE 用户表 SET 验证码 = '', 用户状态 = 'normal', 尝试次数 = 0 WHERE 用户名称 = %s",
            (username,)
        )
        conn.commit()

        return jsonify({"message": "邮箱验证成功"}), 200

    except pymysql.Error as e:
        # 发生错误时回滚
        if 'conn' in locals():
            conn.rollback()
        return jsonify({"message": f"数据库错误: {str(e)}"}), 500

    finally:
        # 确保关闭游标和连接
        if 'cursor' in locals():
            cursor.close()
        if 'conn' in locals():
            conn.close()

@app.route('/login', methods=['POST'])
def login():
    data = request.json
    username = data.get('username')
    password = data.get('password')
    if not username or not password:
        return jsonify({"message": "请求错误"}), 400
    # 如果用户名里面有@，则认为是邮箱
    if '@' in username:
        email = username
        try:
            # 获取数据库连接
            conn = get_db_connection()
            cursor = conn.cursor()

            # 查询用户信息
            cursor.execute(
                "SELECT 用户名称,用户密码,尝试次数,用户状态 FROM 用户表 WHERE 用户邮箱 = %s",
                (email,)
            )
            user_data = cursor.fetchone()
            if not user_data:
                return jsonify({"message": "账号没有注册"}), 400
            else:
                try:
                    username=user_data["用户名称"]
                    db_password=user_data["用户密码"]
                    test_times=user_data["尝试次数"]
                    db_statu=user_data["用户状态"]
                    if db_statu=="create":
                        return jsonify({"message": "用户未激活，请激活后使用"}), 400
                    if test_times>=5:
                        return jsonify({"message": "尝试次数过多请24小时后再进行尝试"}), 400
                    if not(check_password_hash(db_password,password)):
                        cursor.execute(
                            "UPDATE 用户表 SET 尝试次数 = 尝试次数 + 1 WHERE 用户名称 = %s",
                            (username,)
                        )
                        conn.commit()
                        return jsonify({"message": "密码错误"}), 400
                    else:
                        # 验证成功，更新用户状态
                        cursor.execute(
                            "UPDATE 用户表 SET 尝试次数 = 0 WHERE 用户名称 = %s",
                            (username,)
                        )
                        conn.commit()


                        return jsonify({ "message": "欢迎" + username}), 200
                except (ValueError, TypeError) as e:
                    # 处理解包错误
                    return jsonify({"message": "数据解析错误"}), 500
        except pymysql.Error as e:
            # 发生错误时回滚
            if 'conn' in locals():
                conn.rollback()
            return jsonify({"message": f"数据库错误: {str(e)}"}), 500

        finally:
            # 确保关闭游标和连接
            if 'cursor' in locals():
                cursor.close()
            if 'conn' in locals():
                conn.close()
    else:
        try:
            # 获取数据库连接
            conn = get_db_connection()
            cursor = conn.cursor()

            # 查询用户信息
            cursor.execute(
                "SELECT 用户密码,尝试次数,用户状态 FROM 用户表 WHERE 用户名称 = %s",
                (username,)
            )
            user_data = cursor.fetchone()
            if not user_data:
                return jsonify({"message": "账号没有注册"}), 400
            else:
                try:
                    db_password = user_data["用户密码"]
                    test_times = user_data["尝试次数"]
                    db_statu = user_data["用户状态"]
                    if db_statu == "create":
                        return jsonify({"message": "用户未激活，请激活后使用"}), 400
                    if test_times >= 5:
                        return jsonify({"message": "尝试次数过多请24小时后再进行尝试"}), 400
                    if not(check_password_hash(db_password,password)):
                        cursor.execute(
                            "UPDATE 用户表 SET 尝试次数 = 尝试次数 + 1 WHERE 用户名称 = %s",
                            (username,)
                        )
                        conn.commit()
                        return jsonify({"message": "密码错误"}), 400
                    else:
                        cursor.execute(
                            "UPDATE 用户表 SET 尝试次数 = 0 WHERE 用户名称 = %s",
                            (username,)
                        )
                        conn.commit()


                        return jsonify({"message": "欢迎"+username}), 200
                except (ValueError, TypeError) as e:
                    # 处理解包错误
                    return jsonify({"message": "数据解析错误"}), 500
        except pymysql.Error as e:
            # 发生错误时回滚
            if 'conn' in locals():
                conn.rollback()
            return jsonify({"message": f"数据库错误: {str(e)}"}), 500

        finally:
            # 确保关闭游标和连接
            if 'cursor' in locals():
                cursor.close()
            if 'conn' in locals():
                conn.close()

@app.route('/reset', methods=['POST'])#重置密码
def reset():
    data = request.json
    username = data.get('username')
    code = data.get('code')
    password = data.get('password')
    if not username or not password or not code:
        return jsonify({"message": "请求错误"}), 400
    if len(password) < 6:
        return jsonify({"message": "密码过短"}), 400
    else:
        if '@' in username:
            email=username
            try:
                # 获取数据库连接
                conn = get_db_connection()
                cursor = conn.cursor()

                # 查询用户信息
                cursor.execute(
                    "SELECT 验证码 FROM 用户表 WHERE 用户邮箱 = %s",
                    (email,)
                )
                user_data = cursor.fetchone()
                if not user_data:
                    return jsonify({"message": "账号没有注册"}), 400
                else:
                    try:
                        db_code=user_data["验证码"]
                        if db_code !=code or db_code=='':
                            return jsonify({"message": "验证码错误"}), 400
                        else:
                            hash_password=generate_password_hash(password)
                            cursor.execute(
                                "UPDATE 用户表 SET 用户密码 = %s, 验证码=''  WHERE 用户邮箱 = %s",
                                (hash_password,email,)
                            )
                            return jsonify({"message": "密码修改成功"}), 200
                    except (ValueError, TypeError) as e:
                        # 处理解包错误
                        return jsonify({"message": "数据解析错误"}), 500
            except pymysql.Error as e:
                # 发生错误时回滚
                if 'conn' in locals():
                    conn.rollback()
                return jsonify({"message": f"数据库错误: {str(e)}"}), 500

            finally:
                # 确保关闭游标和连接
                if 'cursor' in locals():
                    cursor.close()
                if 'conn' in locals():
                    conn.close()
        else:
            try:
                # 获取数据库连接
                conn = get_db_connection()
                cursor = conn.cursor()

                # 查询用户信息
                cursor.execute(
                    "SELECT 验证码 FROM 用户表 WHERE 用户名称 = %s",
                    (username,)
                )
                user_data = cursor.fetchone()
                if not user_data:
                    return jsonify({"message": "账号没有注册"}), 400
                else:
                    try:
                        db_code = user_data["验证码"]
                        if db_code != code or db_code=='':
                            return jsonify({"message": "验证码错误"}), 400
                        else:
                            hash_password = generate_password_hash(password)
                            cursor.execute(
                                "UPDATE 用户表 SET 用户密码 = %s, 验证码=''  WHERE 用户名称 = %s",
                                (hash_password, username,)
                            )
                            return jsonify({"message": "密码修改成功"}), 200
                    except (ValueError, TypeError) as e:
                        # 处理解包错误
                        return jsonify({"message": "数据解析错误"}), 500
            except pymysql.Error as e:
                # 发生错误时回滚
                if 'conn' in locals():
                    conn.rollback()
                return jsonify({"message": f"数据库错误: {str(e)}"}), 500

            finally:
                # 确保关闭游标和连接
                if 'cursor' in locals():
                    cursor.close()
                if 'conn' in locals():
                    conn.close()


@app.route('/send_reset_code', methods=['POST'])#发送验证码
def send_reset_code():
    data = request.json
    username = data.get('username')
    if '@' in username:
        email = username
        try:
            # 获取数据库连接
            conn = get_db_connection()
            cursor = conn.cursor()

            cursor.execute(
                "SELECT 用户名称,验证码 FROM 用户表 WHERE 用户邮箱 = %s",
                (email,)
             )
            user_data = cursor.fetchone()
            if not user_data:
                return jsonify({"message": "账号没有注册"}), 400
            else:
                try:
                    user_code=user_data["验证码"]
                    if user_code != '':
                        return jsonify({"message": "验证码已发送，请勿重复发送"}), 400
                    verification_code = generate_verification_code()
                    cursor.execute(
                    "UPDATE 用户表 SET 验证码 = %s  WHERE 用户邮箱 = %s",
                    (verification_code,email,)
                    )
                    msg = Message("SoundSight账号验证码", recipients=[email])
                    msg.body = f'您正在重置SoundSight账号密码，您的验证码为 {verification_code}如果不是本人操作，请忽略本次邮件,本邮件有效期24小时'
                    mail.send(msg)
                    return jsonify({"message": "验证码已发送，请前往邮箱查看"}), 200
                except(ValueError, TypeError) as e:
                    # 处理解包错误
                    return jsonify({"message": "数据解析错误"}), 500
        except pymysql.Error as e:
            # 发生错误时回滚
            if 'conn' in locals():
                conn.rollback()
            return jsonify({"message": f"数据库错误: {str(e)}"}), 500

        finally:
            # 确保关闭游标和连接
            if 'cursor' in locals():
                cursor.close()
            if 'conn' in locals():
                conn.close()

    else:
        try:
            # 获取数据库连接
            conn = get_db_connection()
            cursor = conn.cursor()

            cursor.execute(
                "SELECT 用户邮箱,验证码 FROM 用户表 WHERE 用户名称 = %s",
                (username,)
            )
            user_data = cursor.fetchone()
            if not user_data:
                return jsonify({"message": "账号没有注册"}), 400
            else:
                try:
                    user_code = user_data["验证码"]
                    email = user_data["用户邮箱"]
                    if user_code != '':
                        return jsonify({"message": "验证码已发送，请勿重复发送"}), 400
                    verification_code = generate_verification_code()
                    cursor.execute(
                    "UPDATE 用户表 SET 验证码 = %s  WHERE 用户邮箱 = %s",
                    (verification_code,email,)
                    )
                    msg = Message("SoundSight账号验证码", recipients=[email])
                    msg.body = f'您正在重置SoundSight账号密码，您的验证码为 {verification_code}如果不是本人操作，请忽略本次邮件,本邮件有效期24小时'
                    mail.send(msg)
                    return jsonify({"message": "验证码已发送，请前往邮箱查看"}), 200
                except(ValueError, TypeError) as e:
                    # 处理解包错误
                    return jsonify({"message": "数据解析错误"}), 500
        except pymysql.Error as e:
            # 发生错误时回滚
            if 'conn' in locals():
                conn.rollback()
            return jsonify({"message": f"数据库错误: {str(e)}"}), 500

        finally:
            # 确保关闭游标和连接
            if 'cursor' in locals():
                cursor.close()
            if 'conn' in locals():
                conn.close()

@app.route('/create_order', methods=['POST'])
def create_order():
    order_data = request.get_json()  # 自动解析请求体中的JSON
    if not order_data:
        return jsonify({"message": "传输数据错误"}), 400

    # 2. 验证必填字段
    required_fields = ['username', 'password','items']
    for field in required_fields:
        if field not in order_data:
            return jsonify({"message": f"缺失必要选项: {field}"}), 400



    # 3. 验证商品项
    if not isinstance(order_data['items'], list) or len(order_data['items']) == 0:
        return jsonify({"message": "没有商品项目"}), 400

    username = order_data['username']
    password = order_data['password']

    if not username or not password:
        return jsonify({"message": "请提供用户名和密码"}), 400
    try:
        # 获取数据库连接
        conn = get_db_connection()
        cursor = conn.cursor()

        cursor.execute(
            "SELECT 用户ID,用户密码 FROM 用户表 WHERE 用户名称 = %s ",
            (username,)
        )
        user_data = cursor.fetchone()
        if not user_data:
            return jsonify({"message": "账号不存在，请联系客服处理"}), 400
        try:
            userID = user_data["用户ID"]
            db_password = user_data["用户密码"]
            if check_password_hash(db_password, password):
                order_id = 'ORD-' + str(uuid.uuid4())[:8].upper()

                # 5. 计算总金额并验证商品
                total_amount = 0.0
                validated_items = []

                items = order_data['items']
                for item in order_data['items']:
                    # 验证商品项结构
                    if 'productId' not in item or 'quantity' not in item:
                        return jsonify({"message": "需要有商品和数量"}), 400

                    if item['quantity'] <= 0:
                        return jsonify({"message": f"商品数量错误 {product_id}"}), 400

                    product_id = item['productId']
                    quantity = item['quantity']
                    cursor = conn.cursor()

                    cursor.execute(
                        "SELECT 商品ID, 商品名称, 商品价格, 商品数量 FROM 商品 WHERE 商品ID = %s",
                        (product_id,)
                    )

                    product = cursor.fetchone()


                    if not product:
                        return jsonify({"message": f"商品 {product_id} 没有找到"}), 404
                    product_name = product['商品名称']
                        # 检查库存
                    if quantity > product['商品数量']:
                        return jsonify({
                            "message": f"抱歉商品库存不足 {product_name}",
                            "available": product['商品数量']
                        }), 400

                    # 计算单项总价
                    price = float(product['商品价格'])
                    item_total = price * quantity

                    # 构建验证后的商品项
                    validated_item = {
                        'productId': product_id,
                        'name': product['商品名称'],
                        'quantity': quantity,
                        'price': price,
                        'itemTotal': item_total
                    }

                    validated_items.append(validated_item)
                    total_amount += item_total



                try:

                    # 插入订单主表/后面改为update/因为要保留客户订单ID防止冲突
                    cursor.execute(
                        """INSERT INTO 订单表
                           ( 订单ID,购买者ID, 订单状态,订单总金额) 
                           VALUES (%s, %s, %s,%s)""",
                        (order_id,userID, '待支付',total_amount)
                    )

                    # 9. 插入订单明细
                    for item in validated_items:
                        cursor.execute(
                            """INSERT INTO 购买数量表 
                               (订单ID,商品ID,商品数量) 
                               VALUES (%s, %s, %s)""",
                            (order_id, item['productId'],item['quantity'])
                        )

                        # 10. 更新库存
                        cursor.execute(
                            "UPDATE 商品 SET 商品数量 = 商品数量 - %s WHERE 商品ID = %s",
                            (item['quantity'], item['productId'])
                        )

                    # 提交事务
                    conn.commit()


                    # 9. 返回成功响应
                    return jsonify({
                        "status": "success",
                        "orderId": order_id,
                        "message": "订单创建成功"
                    }), 201

                except Exception as e:
                    # 处理意外错误
                    return jsonify({"error": str(e)}), 500
        except(ValueError, TypeError) as e:
            # 处理解包错误
            return jsonify({"message": "数据解析错误"}), 500
    except pymysql.Error as e:
        # 发生错误时回滚
        if 'conn' in locals():
            conn.rollback()
        return jsonify({"message": f"数据库错误: {str(e)}"}), 500

    finally:
        # 确保关闭游标和连接
        if 'cursor' in locals():
            cursor.close()
        if 'conn' in locals():
            conn.close()


@app.route('/upload_audio', methods=['POST'])
def handle_audio():


    username = request.form.get('username')
    password = request.form.get('password')
    if not username or not password:
        return jsonify({"message": "请提供用户名和密码"}), 400



    if 'audio' not in request.files:
        return jsonify({"error": "无文件上传"}), 400
    audio_file = request.files['audio']
    if audio_file.filename == '':
        return jsonify({"message": "请不要上传空文件"}), 400
    if username=='':
        return jsonify({"message": "请登录后进行尝试"}), 400

    try:
        # 获取数据库连接
        conn = get_db_connection()
        cursor = conn.cursor()

        cursor.execute(
            "SELECT 用户ID,用户密码 FROM 用户表 WHERE 用户名称 = %s ",
            (username,)
        )
        user_data = cursor.fetchone()
        if not user_data:
            return jsonify({"message": "账号不存在，请联系客服处理"}), 400
        try:
            userID=user_data["用户ID"]
            db_password=user_data["用户密码"]
            if check_password_hash(db_password,password):
                cursor.execute(
                    "SELECT 用户剩余使用次数 FROM 剩余使用次数 WHERE 用户ID = %s",
                    (userID,)
                )
                user_data2=cursor.fetchone()
                if not user_data2:
                    cursor.execute(
                        "INSERT INTO 剩余使用次数 (用户剩余使用次数,用户ID) values (%s,%s) ",
                        (5,userID,)
                    )
                    conn.commit()
                    cursor.execute(
                        "SELECT 用户剩余使用次数 FROM 用户表 WHERE 用户ID = %s",
                        (userID,)
                    )
                    user_data2 = cursor.fetchone()
                use_times=user_data2["用户剩余使用次数"]
                if use_times<1:
                    return jsonify({"message": "剩余使用次数为0，请到商城界面购买使用次数"}),400

                # 保存音频文件
                filename = secure_filename(audio_file.filename)
                save_path = os.path.join(app.config['UPLOAD_FOLDER'], filename)
                audio_file.save(save_path)

                # 调用生成函数（从请求参数获取或使用默认值）
                # number = int(request.form.get('number', 1))
                # size = request.form.get('size', '1024*1024')

                # img_url, description = transform(save_path, number, size)
                img_url, description = transform(save_path)
                # 返回结果
                cursor.execute(
                    "UPDATE 剩余使用次数 SET 用户剩余使用次数=用户剩余使用次数-1 WHERE 用户ID=%s ",
                    (userID,)
                )
                conn.commit()
                return jsonify({
                    "image_url": img_url,
                    "description": description
                })

            else:
                return jsonify({"message":"密码错误"}),400
        except(ValueError, TypeError) as e:
            # 处理解包错误
            return jsonify({"message": "数据解析错误"}), 500
    except pymysql.Error as e:
        # 发生错误时回滚
        if 'conn' in locals():
            conn.rollback()
        return jsonify({"message": f"数据库错误: {str(e)}"}), 500

    finally:
        # 确保关闭游标和连接
        if 'cursor' in locals():
            cursor.close()
        if 'conn' in locals():
            conn.close()


@app.route('/get_products', methods=['POST'])
def get_products():
    """获取所有商品"""
    try:
        # 获取请求数据
        data = request.get_json()
        if not data or 'username' not in data or 'password' not in data:
            return jsonify({'error': '用户名和密码必填'}), 400

        username = data['username']
        password = data['password']
        try:
            # 获取数据库连接
            conn = get_db_connection()
            cursor = conn.cursor()

            cursor.execute(
                "SELECT 用户ID,用户密码 FROM 用户表 WHERE 用户名称 = %s ",
                (username,)
            )
            user_data = cursor.fetchone()
            if not user_data:
                return jsonify({"message": "账号不存在，请联系客服处理"}), 400
            try:
                userID = user_data["用户ID"]
                db_password = user_data["用户密码"]
                if check_password_hash(db_password, password):
                    # 查询商品
                    cursor.execute("SELECT * FROM 商品")
                    shopping_data2 = cursor.fetchall()


                    return jsonify({'products': shopping_data2}), 200
            except(ValueError, TypeError) as e:
                # 处理解包错误
                return jsonify({"message": "数据解析错误"}), 500
        except pymysql.Error as e:
            # 发生错误时回滚
            if 'conn' in locals():
                conn.rollback()
            return jsonify({"message": f"数据库错误: {str(e)}"}), 500

        finally:
            # 确保关闭游标和连接
            if 'cursor' in locals():
                cursor.close()
            if 'conn' in locals():
                conn.close()


    except Exception as e:
        print(f"Error: {e}")
        return jsonify({'error': '服务器错误'}), 500

@app.route('/get_notice', methods=['POST'])
def get_notice():
    """获取所有商品"""
    try:
        # 获取请求数据
        data = request.get_json()
        if not data or 'username' not in data or 'password' not in data:
            return jsonify({'error': '用户名和密码必填'}), 400

        username = data['username']
        password = data['password']
        try:
            # 获取数据库连接
            conn = get_db_connection()
            cursor = conn.cursor()

            cursor.execute(
                "SELECT 用户ID,用户密码 FROM 用户表 WHERE 用户名称 = %s ",
                (username,)
            )
            user_data = cursor.fetchone()
            if not user_data:
                return jsonify({"message": "账号不存在，请联系客服处理"}), 400
            try:
                userID = user_data["用户ID"]
                db_password = user_data["用户密码"]
                if check_password_hash(db_password, password):
                    # 查询商品
                    cursor.execute("SELECT * FROM 公告表")
                    notice_data2 = cursor.fetchall()


                    return jsonify({'notices': notice_data2}), 200
            except(ValueError, TypeError) as e:
                # 处理解包错误
                return jsonify({"message": "数据解析错误"}), 500
        except pymysql.Error as e:
            # 发生错误时回滚
            if 'conn' in locals():
                conn.rollback()
            return jsonify({"message": f"数据库错误: {str(e)}"}), 500

        finally:
            # 确保关闭游标和连接
            if 'cursor' in locals():
                cursor.close()
            if 'conn' in locals():
                conn.close()


    except Exception as e:
        print(f"Error: {e}")
        return jsonify({'error': '服务器错误'}), 500


@app.route('/get_orders', methods=['POST'])
def get_orders():
    """获取所有商品"""
    try:
        # 获取请求数据
        data = request.get_json()
        if not data or 'username' not in data or 'password' not in data:
            return jsonify({'error': '用户名和密码必填'}), 400

        username = data['username']
        password = data['password']
        try:
            # 获取数据库连接
            conn = get_db_connection()
            cursor = conn.cursor()

            cursor.execute(
                "SELECT 用户ID,用户密码 FROM 用户表 WHERE 用户名称 = %s ",
                (username,)
            )
            user_data = cursor.fetchone()
            if not user_data:
                return jsonify({"message": "账号不存在，请联系客服处理"}), 400
            try:
                userID = user_data["用户ID"]
                db_password = user_data["用户密码"]
                if check_password_hash(db_password, password):
                    # 查询订单
                    cursor.execute(
                        "SELECT * FROM 订单表 WHERE 购买者ID = %s",
                        (userID)
                    )
                    orders = cursor.fetchall()

                    # 准备最终返回的订单列表
                    orders_with_items = []

                    for order in orders:
                        order_id = order['订单ID']  # 假设订单ID字段名为'订单ID'

                        # 查询该订单的商品列表
                        cursor.execute(
                            """SELECT 购买数量表.商品ID, 购买数量表.商品数量, 商品.商品名称, 商品.商品价格, 商品.商品图片,商品.商品介绍
                               FROM 购买数量表 
                               JOIN 商品 ON 购买数量表.商品ID = 商品.商品ID 
                               WHERE 购买数量表.订单ID = %s""",
                            (order_id,)
                        )
                        items = cursor.fetchall()

                        # 将订单信息和商品列表合并
                        order_with_items = dict(order)  # 将订单元组转为字典
                        order_with_items['商品列表'] = items
                        orders_with_items.append(order_with_items)

                    return jsonify({'orders': orders_with_items}), 200
            except(ValueError, TypeError) as e:
                # 处理解包错误
                return jsonify({"message": "数据解析错误"}), 500
        except pymysql.Error as e:
            # 发生错误时回滚
            if 'conn' in locals():
                conn.rollback()
            return jsonify({"message": f"数据库错误: {str(e)}"}), 500

        finally:
            # 确保关闭游标和连接
            if 'cursor' in locals():
                cursor.close()
            if 'conn' in locals():
                conn.close()


    except Exception as e:
        print(f"Error: {e}")
        return jsonify({'error': '服务器错误'}), 500
@app.route('/cancel_order', methods=['POST'])
def cancel_order():
    """获取所有商品"""
    try:
        # 获取请求数据
        data = request.get_json()
        if not data or 'username' not in data or 'password' not in data:
            return jsonify({'error': '用户名和密码必填'}), 400

        username = data['username']
        password = data['password']
        orderID = data['orderID']
        try:
            # 获取数据库连接
            conn = get_db_connection()
            cursor = conn.cursor()

            cursor.execute(
                "SELECT 用户ID,用户密码 FROM 用户表 WHERE 用户名称 = %s ",
                (username,)
            )
            user_data = cursor.fetchone()
            if not user_data:
                return jsonify({"message": "账号不存在，请联系客服处理"}), 400
            try:
                userID = user_data["用户ID"]
                db_password = user_data["用户密码"]
                if check_password_hash(db_password, password):
                    # 查询订单
                    cursor.execute(
                        "SELECT * FROM 订单表 WHERE 订单ID = %s AND 购买者ID = %s AND 订单状态 = '待支付' LIMIT 1",
                        (orderID, userID)
                    )
                    order = cursor.fetchone()
                    #如果存在订单并且订单状态为待支付，则删除数据库订单表中和购买数量表中的数据
                    if not order:
                        return jsonify({"message": "订单不存在或不属于该用户"}), 400

                        # 检查订单状态是否为"待支付"
                    if order["订单状态"] != "待支付":
                        return jsonify({"message": "只能取消未支付的订单"}), 400

                    try:
                        # 开始事务
                        conn.begin()

                        # 先删除购买数量表中的相关记录
                        cursor.execute(
                            "DELETE FROM 购买数量表 WHERE 订单ID = %s",
                            (orderID,)
                        )

                        # 再删除订单表中的记录
                        cursor.execute(
                            "DELETE FROM 订单表 WHERE 订单ID = %s",
                            (orderID,)
                        )

                        # 提交事务
                        conn.commit()

                        return jsonify({"message": "订单取消成功"}), 200
                    except pymysql.Error as e:
                        # 发生错误时回滚
                        conn.rollback()
                        print("出错1")
                        return jsonify({"message": f"取消订单失败: {str(e)}"}), 500


            except(ValueError, TypeError) as e:
                # 处理解包错误
                print("出错2")
                return jsonify({"message": "数据解析错误"}), 500
        except pymysql.Error as e:
            # 发生错误时回滚
            if 'conn' in locals():
                conn.rollback()
                print("出错3")
            return jsonify({"message": f"数据库错误: {str(e)}"}), 500

        finally:
            # 确保关闭游标和连接
            if 'cursor' in locals():
                cursor.close()
            if 'conn' in locals():
                conn.close()


    except Exception as e:
        print(f"Error: {e}")

        return jsonify({'error': '服务器错误'}), 500

@app.route('/confirm_order', methods=['POST'])
def confirm_order():
    """获取所有商品"""
    try:
        # 获取请求数据
        data = request.get_json()
        if not data or 'username' not in data or 'password' not in data:
            return jsonify({'error': '用户名和密码必填'}), 400

        username = data['username']
        password = data['password']
        orderID = data['orderID']
        try:
            # 获取数据库连接
            conn = get_db_connection()
            cursor = conn.cursor()

            cursor.execute(
                "SELECT 用户ID,用户密码 FROM 用户表 WHERE 用户名称 = %s ",
                (username,)
            )
            user_data = cursor.fetchone()
            if not user_data:
                return jsonify({"message": "账号不存在，请联系客服处理"}), 400
            try:
                userID = user_data["用户ID"]
                db_password = user_data["用户密码"]
                if check_password_hash(db_password, password):
                    # 查询订单
                    cursor.execute(
                        "SELECT * FROM 订单表 WHERE 订单ID = %s AND 购买者ID = %s AND 订单状态 = '待收货' LIMIT 1",
                        (orderID, userID)
                    )
                    order = cursor.fetchone()

                    if not order:
                        return jsonify({"message": "订单不存在或不属于该用户"}), 400

                        # 检查订单状态是否为"待收货"
                    if order["订单状态"] != "待收货":
                        return jsonify({"message": "请选择待收货的订单"}), 400

                    try:
                        # 开始事务
                        conn.begin()

                        # 更新订单状态为"已完成"
                        cursor.execute(
                            "UPDATE 订单表 SET 订单状态 = '待评价', 确认收货时间 = NOW() WHERE 订单ID = %s",
                            (orderID,)
                        )

                        # 提交事务
                        conn.commit()

                        return jsonify({"message": "确认收货成功"}), 200
                    except pymysql.Error as e:
                        # 发生错误时回滚
                        conn.rollback()
                        print("出错1")
                        return jsonify({"message": f"确认收货失败: {str(e)}"}), 500


            except(ValueError, TypeError) as e:
                # 处理解包错误
                print("出错2")
                return jsonify({"message": "数据解析错误"}), 500
        except pymysql.Error as e:
            # 发生错误时回滚
            if 'conn' in locals():
                conn.rollback()
                print("出错3")
            return jsonify({"message": f"数据库错误: {str(e)}"}), 500

        finally:
            # 确保关闭游标和连接
            if 'cursor' in locals():
                cursor.close()
            if 'conn' in locals():
                conn.close()


    except Exception as e:
        print(f"Error: {e}")

        return jsonify({'error': '服务器错误'}), 500

@app.route('/evaluate_order', methods=['POST'])
def evalute_order():
    """获取所有商品"""
    try:
        # 获取请求数据
        data = request.get_json()
        if not data or 'username' not in data or 'password' not in data:
            return jsonify({'error': '用户名和密码必填'}), 400

        username = data['username']
        password = data['password']
        orderID = data['orderID']
        rating=data['rating']
        try:
            # 获取数据库连接
            conn = get_db_connection()
            cursor = conn.cursor()

            cursor.execute(
                "SELECT 用户ID,用户密码 FROM 用户表 WHERE 用户名称 = %s ",
                (username,)
            )
            user_data = cursor.fetchone()
            if not user_data:
                return jsonify({"message": "账号不存在，请联系客服处理"}), 400
            try:
                userID = user_data["用户ID"]
                db_password = user_data["用户密码"]
                if check_password_hash(db_password, password):
                    # 查询订单
                    cursor.execute(
                        "SELECT * FROM 订单表 WHERE 订单ID = %s AND 购买者ID = %s AND 订单状态 = '待评价' LIMIT 1",
                        (orderID, userID)
                    )
                    order = cursor.fetchone()

                    if not order:
                        return jsonify({"message": "订单不存在或不属于该用户"}), 400

                        # 检查订单状态是否为"待收货"
                    if order["订单状态"] != "待评价":
                        return jsonify({"message": "请选择待评价的订单"}), 400

                    try:
                        # 开始事务
                        conn.begin()

                        # 更新订单状态为"已完成"
                        cursor.execute(
                            "UPDATE 订单表 SET 订单状态 = '已完成', 订单评分 = %s WHERE 订单ID = %s",
                            (rating,orderID)
                        )

                        # 提交事务
                        conn.commit()

                        return jsonify({"message": "订单评价成功"}), 200
                    except pymysql.Error as e:
                        # 发生错误时回滚
                        conn.rollback()
                        print("出错1")
                        return jsonify({"message": f"订单评价失败: {str(e)}"}), 500


            except(ValueError, TypeError) as e:
                # 处理解包错误
                print("出错2")
                return jsonify({"message": "数据解析错误"}), 500
        except pymysql.Error as e:
            # 发生错误时回滚
            if 'conn' in locals():
                conn.rollback()
                print("出错3")
            return jsonify({"message": f"数据库错误: {str(e)}"}), 500

        finally:
            # 确保关闭游标和连接
            if 'cursor' in locals():
                cursor.close()
            if 'conn' in locals():
                conn.close()


    except Exception as e:
        print(f"Error: {e}")

        return jsonify({'error': '服务器错误'}), 500


@app.route('/get_user_info', methods=['POST'])
def get_user():
    """获取所有商品"""
    try:
        # 获取请求数据
        data = request.get_json()
        if not data or 'username' not in data or 'password' not in data:
            return jsonify({'error': '用户名和密码必填'}), 400

        username = data['username']
        password = data['password']
        try:
            # 获取数据库连接
            conn = get_db_connection()
            cursor = conn.cursor()

            cursor.execute(
                "SELECT 用户ID,用户密码 FROM 用户表 WHERE 用户名称 = %s ",
                        (username)
            )
            user_data = cursor.fetchone()
            if not user_data:
                return jsonify({"message": "账号不存在，请联系客服处理"}), 400
            try:
                userID = user_data["用户ID"]
                db_password = user_data["用户密码"]
                if check_password_hash(db_password, password):
                    # 查询用户
                    cursor.execute(
                        "SELECT 用户名称,用户等级,用户头像,被点赞数 FROM 用户表 WHERE 用户ID = %s",
                        (userID)
                    )
                    user_info = cursor.fetchone()
                    # 返回用户名称，用户等级，用户头像
                    return jsonify({
                        "username": user_info["用户名称"],
                        "user_level": user_info["用户等级"],
                        "avatar": user_info["用户头像"],
                        "thumbs_up_numbers":user_info["被点赞数"]
                    }), 200
                else:
                    return jsonify({"message": "密码错误"}), 400




            except(ValueError, TypeError) as e:
                # 处理解包错误
                print("出错2")
                return jsonify({"message": "数据解析错误"}), 500
        except pymysql.Error as e:
            # 发生错误时回滚
            if 'conn' in locals():
                conn.rollback()
                print("出错3")
            return jsonify({"message": f"数据库错误: {str(e)}"}), 500

        finally:
            # 确保关闭游标和连接
            if 'cursor' in locals():
                cursor.close()
            if 'conn' in locals():
                conn.close()


    except Exception as e:
        print(f"Error: {e}")

        return jsonify({'error': '服务器错误'}), 500
@app.route('/save_audio', methods=['POST'])
def save_audio():
    ALLOWED_AUDIO_EXTENSIONS = {'mp3', 'wav', 'ogg', 'aac', 'flac', 'm4a'}

    username = request.form.get('username')
    password = request.form.get('password')
    if not username or not password:
        return jsonify({"message": "请提供用户名和密码"}), 400



    if 'audio' not in request.files:
        return jsonify({"error": "无文件上传"}), 400
    audio_file = request.files['audio']
    if audio_file.filename == '':
        return jsonify({"message": "请不要上传空文件"}), 400
    if username=='':
        return jsonify({"message": "请登录后进行尝试"}), 400
    # 添加文件类型检查
    if not allowed_file(audio_file.filename, ALLOWED_AUDIO_EXTENSIONS):
        return jsonify({
            "message": "不支持的文件类型",
            "allowed_types": list(ALLOWED_AUDIO_EXTENSIONS)
        }), 400
    if audio_file.content_length > app.config['MAX_AUDIO_SIZE']:
        return jsonify({"message": "音频文件太大"}), 400

    try:
        # 获取数据库连接
        conn = get_db_connection()
        cursor = conn.cursor()

        cursor.execute(
            "SELECT 用户ID,用户密码 FROM 用户表 WHERE 用户名称 = %s ",
            (username,)
        )
        user_data = cursor.fetchone()
        if not user_data:
            return jsonify({"message": "账号不存在，请联系客服处理"}), 400
        try:
            userID=user_data["用户ID"]
            db_password=user_data["用户密码"]
            if check_password_hash(db_password,password):
               #将文件存储到/uploads/audio 中并保存相关信息到数据库
               # 确保上传目录存在
               upload_dir = os.path.join('uploads', 'audio')
               os.makedirs(upload_dir, exist_ok=True)

               # 生成安全的文件名
               filename = secure_filename(audio_file.filename)
               # 添加时间戳防止文件名冲突
               timestamp = datetime.now().strftime("%Y%m%d%H%M%S")
               unique_filename = f"{timestamp}_{filename}"
               filepath = os.path.join(upload_dir, unique_filename)

               # 保存文件
               audio_file.save(filepath)

               # 获取文件信息
               file_size = os.path.getsize(filepath)  # 文件大小(字节)
               file_type = filename.rsplit('.', 1)[1].lower() if '.' in filename else ''  # 文件扩展名

               # 将文件信息存入数据库
               cursor.execute(
                   "INSERT INTO 文件表_audio (文件名称, 文件路径, 文件大小, 文件类型, 文件上传者ID) VALUES (%s, %s, %s, %s, %s)",
                   (filename, filepath, file_size, file_type, userID)
               )
               conn.commit()

               return jsonify({
                   "message": "文件上传成功",
                   "filename": filename,
                   "filepath": filepath,
                   "size": file_size,
                   "type": file_type
               }), 200

            else:
                return jsonify({"message":"密码错误"}),400
        except(ValueError, TypeError) as e:
            # 处理解包错误
            return jsonify({"message": "数据解析错误"}), 500
    except pymysql.Error as e:
        # 发生错误时回滚
        if 'conn' in locals():
            conn.rollback()
        return jsonify({"message": f"数据库错误: {str(e)}"}), 500

    finally:
        # 确保关闭游标和连接
        if 'cursor' in locals():
            cursor.close()
        if 'conn' in locals():
            conn.close()


@app.route('/save_image', methods=['POST'])
def save_image():
    ALLOWED_IMAGE_EXTENSIONS= {'png', 'jpg', 'jpeg', 'gif', 'bmp', 'webp'}


    username = request.form.get('username')
    password = request.form.get('password')
    if not username or not password:
        return jsonify({"message": "请提供用户名和密码"}), 400

    if 'image' not in request.files:
        return jsonify({"error": "无图片文件上传"}), 400

    image_file = request.files['image']
    if image_file.filename == '':
        return jsonify({"message": "请不要上传空文件"}), 400

    if username == '':
        return jsonify({"message": "请登录后进行尝试"}), 400
        # 添加文件类型检查
    if not allowed_file(image_file.filename,ALLOWED_IMAGE_EXTENSIONS):
        return jsonify({
            "message": "不支持的文件类型",
            "allowed_types": list(ALLOWED_IMAGE_EXTENSIONS)
        }), 400
    if image_file.content_length > app.config['MAX_IMAGE_SIZE']:
        return jsonify({"message": "图片文件太大"}), 400

    try:
        # 获取数据库连接
        conn = get_db_connection()
        cursor = conn.cursor()

        # 验证用户
        cursor.execute(
            "SELECT 用户ID,用户密码 FROM 用户表 WHERE 用户名称 = %s",
            (username,)
        )
        user_data = cursor.fetchone()
        if not user_data:
            return jsonify({"message": "账号不存在，请联系客服处理"}), 400

        userID = user_data["用户ID"]
        db_password = user_data["用户密码"]

        if not check_password_hash(db_password, password):
            return jsonify({"message": "密码错误"}), 400

        # 专门处理图片上传
        upload_dir = os.path.join('uploads', 'images')  # 图片保存到images文件夹
        os.makedirs(upload_dir, exist_ok=True)

        # 生成安全的文件名
        filename = secure_filename(image_file.filename)
        timestamp = datetime.now().strftime("%Y%m%d%H%M%S")
        unique_filename = f"{timestamp}_{filename}"
        filepath = os.path.join(upload_dir, unique_filename)


        # 保存图片文件
        image_file.save(filepath)

        # 获取文件信息
        file_size = os.path.getsize(filepath)
        file_type = filename.rsplit('.', 1)[1].lower() if '.' in filename else ''

        # 将文件信息存入数据库
        cursor.execute(
            "INSERT INTO 文件表_image (文件名称, 文件路径, 文件大小, 文件类型, 文件上传者ID) VALUES (%s, %s, %s, %s, %s)",

            (filename, filepath, file_size, file_type, userID)  # 文件类别标记为image
        )
        conn.commit()

        return jsonify({
            "message": "图片上传成功",
            "filename": filename,
            "filepath": filepath,
            "size": file_size,
            "type": file_type
        }), 200

    except (ValueError, TypeError) as e:
        return jsonify({"message": "数据解析错误"}), 500
    except pymysql.Error as e:
        if 'conn' in locals():
            conn.rollback()
        return jsonify({"message": f"数据库错误: {str(e)}"}), 500
    finally:
        if 'cursor' in locals():
            cursor.close()
        if 'conn' in locals():
            conn.close()


@app.route('/save_profile_photo', methods=['POST'])
def save_profile_photo():
    ALLOWED_IMAGE_EXTENSIONS = {'png', 'jpg', 'jpeg', 'gif', 'bmp', 'webp'}

    username = request.form.get('username')
    password = request.form.get('password')
    if not username or not password:
        return jsonify({"message": "请提供用户名和密码"}), 400

    if 'image' not in request.files:
        return jsonify({"error": "无图片文件上传"}), 400

    image_file = request.files['image']
    if image_file.filename == '':
        return jsonify({"message": "请不要上传空文件"}), 400

    if username == '':
        return jsonify({"message": "请登录后进行尝试"}), 400
        # 添加文件类型检查
    if not allowed_file(image_file.filename, ALLOWED_IMAGE_EXTENSIONS):
        return jsonify({
            "message": "不支持的文件类型",
            "allowed_types": list(ALLOWED_IMAGE_EXTENSIONS)
        }), 400
    if image_file.content_length > app.config['MAX_PROFILE_IMAGE_SIZE']:
        return jsonify({"message": "图片文件太大"}), 400

    try:
        # 获取数据库连接
        conn = get_db_connection()
        cursor = conn.cursor()

        # 验证用户
        cursor.execute(
            "SELECT 用户ID,用户密码 FROM 用户表 WHERE 用户名称 = %s",
            (username,)
        )
        user_data = cursor.fetchone()
        if not user_data:
            return jsonify({"message": "账号不存在，请联系客服处理"}), 400

        userID = user_data["用户ID"]
        db_password = user_data["用户密码"]

        if not check_password_hash(db_password, password):
            return jsonify({"message": "密码错误"}), 400

        # 专门处理图片上传
        upload_dir = os.path.join('uploads', 'profiles')  # 图片保存到images文件夹
        os.makedirs(upload_dir, exist_ok=True)

        # 生成安全的文件名
        filename = secure_filename(image_file.filename)
        timestamp = datetime.now().strftime("%Y%m%d%H%M%S")
        unique_filename = f"{timestamp}_{filename}"
        filepath = os.path.join(upload_dir, unique_filename)

        # 保存图片文件
        image_file.save(filepath)

        # 获取文件信息
        file_size = os.path.getsize(filepath)
        file_type = filename.rsplit('.', 1)[1].lower() if '.' in filename else ''

        #（待补全）判断文件表_profile_photo中对应的文件上传者ID是否存在记录，如果有就改为修改记录并删除原有头像文件
        cursor.execute(
            "SELECT 文件路径 FROM 文件表_profile_photo WHERE 文件上传者ID = %s",
            (userID,)
        )
        existing_photo = cursor.fetchone()

        if existing_photo:
            # 删除原有头像文件
            try:
                old_filepath = existing_photo["文件路径"]
                if os.path.exists(old_filepath):
                    os.remove(old_filepath)
            except Exception as e:
                # 如果删除旧文件失败，记录错误但继续执行
                print(f"删除旧头像文件失败: {str(e)}")

            # 更新记录而不是插入新记录
            cursor.execute(
                "UPDATE 文件表_profile_photo SET 文件名称 = %s, 文件路径 = %s, 文件大小 = %s, 文件类型 = %s WHERE 文件上传者ID = %s",
                (filename, filepath, file_size, file_type, userID)
            )
        else:
            # 插入新记录
            cursor.execute(
                "INSERT INTO 文件表_profile_photo (文件名称, 文件路径, 文件大小, 文件类型, 文件上传者ID) VALUES (%s, %s, %s, %s, %s)",
                (filename, filepath, file_size, file_type, userID)
            )
        #在用户表中插入用户头像的URL
        profile_photo_url = unique_filename
        cursor.execute(
            "UPDATE 用户表 SET 用户头像 = %s WHERE 用户ID = %s",
            (profile_photo_url, userID)
        )

        conn.commit()

        return jsonify({
            "message": "图片上传成功",
            "filename": filename,
            "filepath": filepath,
            "size": file_size,
            "type": file_type
        }), 200

    except (ValueError, TypeError) as e:
        return jsonify({"message": "数据解析错误"}), 500
    except pymysql.Error as e:
        if 'conn' in locals():
            conn.rollback()
        return jsonify({"message": f"数据库错误: {str(e)}"}), 500
    finally:
        if 'cursor' in locals():
            cursor.close()
        if 'conn' in locals():
            conn.close()

@app.route('/share_page',methods=['POST'])
def save_share_page():
    ALLOWED_IMAGE_EXTENSIONS = {'png', 'jpg', 'jpeg', 'gif', 'bmp', 'webp'}
    ALLOWED_AUDIO_EXTENSIONS = {'mp3', 'wav', 'ogg', 'aac', 'flac', 'm4a'}

    have_audio = 'false'

    username = request.form.get('username')
    password = request.form.get('password')
    title = request.form.get('title')
    content = request.form.get('content')
    have_audio = request.form.get('have_audio')

    if not username or not password:
        return jsonify({"message": "请提供用户名和密码"}), 400

    if 'image' not in request.files:
        return jsonify({"error": "无图片文件上传"}), 400

    image_file = request.files['image']
    audio_file = None

    if image_file.filename == '':
        return jsonify({"message": "请不要上传空文件"}), 400
    if username == '':
        return jsonify({"message": "请登录后进行尝试"}), 400
        # 添加文件类型检查
    if not allowed_file(image_file.filename, ALLOWED_IMAGE_EXTENSIONS):
        return jsonify({
            "message": "不支持的文件类型",
            "allowed_types": list(ALLOWED_IMAGE_EXTENSIONS)
        }), 400
    if image_file.content_length > app.config['MAX_PROFILE_IMAGE_SIZE']:
        return jsonify({"message": "图片文件太大"}), 400
    if have_audio == 'true':
        audio_file=request.files['audio']
        # 添加文件类型检查
        if not allowed_file(audio_file.filename, ALLOWED_AUDIO_EXTENSIONS):
            return jsonify({
                "message": "不支持的文件类型",
                "allowed_types": list(ALLOWED_AUDIO_EXTENSIONS)
            }), 400
        if audio_file.content_length > app.config['MAX_AUDIO_SIZE']:
            return jsonify({"message": "音频文件太大"}), 400
    try:
        # 获取数据库连接
        conn = get_db_connection()
        cursor = conn.cursor()

        # 验证用户
        cursor.execute(
            "SELECT 用户ID,用户密码 FROM 用户表 WHERE 用户名称 = %s",
            (username,)
        )
        user_data = cursor.fetchone()
        if not user_data:
            return jsonify({"message": "账号不存在，请联系客服处理"}), 400

        userID = user_data["用户ID"]
        db_password = user_data["用户密码"]

        if not check_password_hash(db_password, password):
            return jsonify({"message": "密码错误"}), 400

        # 专门处理图片上传
        upload_dir = os.path.join('uploads', 'images')  # 图片保存到images文件夹
        os.makedirs(upload_dir, exist_ok=True)

        # 生成安全的文件名
        filename = secure_filename(image_file.filename)
        timestamp = datetime.now().strftime("%Y%m%d%H%M%S")
        image_unique_filename = f"{timestamp}_{filename}"
        filepath = os.path.join(upload_dir, image_unique_filename)

        # 保存图片文件
        image_file.save(filepath)

        # 获取文件信息
        file_size = os.path.getsize(filepath)
        file_type = filename.rsplit('.', 1)[1].lower() if '.' in filename else ''

        # 将文件信息存入数据库
        cursor.execute(
            "INSERT INTO 文件表_image (文件名称, 文件路径, 文件大小, 文件类型, 文件上传者ID) VALUES (%s, %s, %s, %s, %s)",
            (filename, filepath, file_size, file_type, userID)  # 文件类别标记为image
        )
        conn.commit()
        audio_unique_filename = None
        #专门处理音频上传
        if have_audio == 'true':
            upload_dir = os.path.join('uploads', 'audio')
            os.makedirs(upload_dir, exist_ok=True)
            # 生成安全的文件名
            filename = secure_filename(audio_file.filename)
            # 添加时间戳防止文件名冲突
            timestamp = datetime.now().strftime("%Y%m%d%H%M%S")
            unique_filename = f"{timestamp}_{filename}"
            audio_unique_filename = unique_filename
            filepath = os.path.join(upload_dir, unique_filename)
            # 保存文件
            audio_file.save(filepath)
            # 获取文件信息
            file_size = os.path.getsize(filepath)  # 文件大小(字节)
            file_type = filename.rsplit('.', 1)[1].lower() if '.' in filename else ''  # 文件扩展名
            # 将文件信息存入数据库
            cursor.execute(
                "INSERT INTO 文件表_audio (文件名称, 文件路径, 文件大小, 文件类型, 文件上传者ID) VALUES (%s, %s, %s, %s, %s)",
                (filename, filepath, file_size, file_type, userID)
            )
            conn.commit()
        #在文章表中插入文章信息
        if have_audio == 'true':
            cursor.execute(
                "INSERT INTO 社区文章 (文章标题, 文章内容, 图片, 音频, 作者ID) VALUES (%s, %s, %s, %s, %s)",
                (title, content, image_unique_filename, audio_unique_filename, userID)
            )
        else:
            cursor.execute(
                "INSERT INTO 社区文章 (文章标题, 文章内容, 图片, 作者ID) VALUES (%s, %s, %s, %s)",
                (title, content, image_unique_filename, userID)
            )
        conn.commit()
        return jsonify({
            "message": "图片上传成功",
        }), 200
    except (ValueError, TypeError) as e:
        return jsonify({"message": "数据解析错误"}), 500
    except pymysql.Error as e:
        if 'conn' in locals():
            conn.rollback()
        return jsonify({"message": f"数据库错误: {str(e)}"}), 500
    finally:
        if 'cursor' in locals():
            cursor.close()
        if 'conn' in locals():
            conn.close()



def allowed_file(filename, allowed_extensions):
    """检查文件扩展名是否在允许的列表中"""
    return '.' in filename and \
           filename.rsplit('.', 1)[1].lower() in allowed_extensions

@app.route('/get_article',methods=['POST'])
def get_article():
    try:
        # 获取请求数据
        data = request.get_json()
        if not data or 'username' not in data or 'password' not in data:
            return jsonify({'error': '用户名和密码必填'}), 400

        username = data['username']
        password = data['password']
        try:
            # 获取数据库连接
            conn = get_db_connection()
            cursor = conn.cursor()

            cursor.execute(
                "SELECT 用户ID,用户密码 FROM 用户表 WHERE 用户名称 = %s ",
                (username,)
            )
            user_data = cursor.fetchone()
            if not user_data:
                return jsonify({"message": "账号不存在，请联系客服处理"}), 400
            try:
                userID = user_data["用户ID"]
                db_password = user_data["用户密码"]
                if check_password_hash(db_password, password):
                    # 从数据库的社区文章表中随机抽取6篇文章作为数据返回
                    cursor.execute(
                        "SELECT * FROM 社区文章 ORDER BY RAND() LIMIT 6"
                    )
                    articles_data = cursor.fetchall()
                    # 获取所有不重复的作者ID
                    author_ids = list({article['作者ID'] for article in articles_data})

                    # 查询这些作者的用户信息
                    author_infos = {}
                    if author_ids:
                        format_strings = ','.join(['%s'] * len(author_ids))
                        cursor.execute(
                            f"SELECT 用户ID, 用户名称, 用户等级, 用户头像 FROM 用户表 WHERE 用户ID IN ({format_strings})",
                            tuple(author_ids)
                        )
                        for author in cursor.fetchall():
                            author_infos[author['用户ID']] = {
                                'name': author['用户名称'],
                                'level': author['用户等级'],
                                'avatar_url': author['用户头像']
                            }

                        # 将文章数据转换为字典列表
                    articles = []
                    for article in articles_data:
                        author_id = article['作者ID']
                        author_info = author_infos.get(author_id, {
                            'name': '未知用户',
                            'level': 0,
                            'avatar_url': None
                        })
                        articles.append({
                            'article_id': article['文章ID'],
                            'title': article['文章标题'],
                            'content': article['文章内容'],
                            'create_time': article['发表时间'].strftime('%Y-%m-%d %H:%M:%S') if article[
                                '发表时间'] else None,
                            'author': {
                                'name': author_info['name'],
                                'level': author_info['level'],
                                'avatar_url': author_info['avatar_url']
                            },

                            'thumbs_up_numbers':article['点赞总数'],
                            'collect_numbers':article['收藏总数'],
                            'audio':article['音频'],
                            'image':article['图片']
                        })

                    return jsonify({'articles': articles}), 200
            except(ValueError, TypeError) as e:
                # 处理解包错误
                return jsonify({"message": "数据解析错误"}), 500
        except pymysql.Error as e:
            # 发生错误时回滚
            if 'conn' in locals():
                conn.rollback()
            return jsonify({"message": f"数据库错误: {str(e)}"}), 500

        finally:
            # 确保关闭游标和连接
            if 'cursor' in locals():
                cursor.close()
            if 'conn' in locals():
                conn.close()


    except Exception as e:
        print(f"Error: {e}")
        return jsonify({'error': '服务器错误'}), 500
@app.route('/get_like_article',methods=['POST'])
def get_like_article():
    try:
        # 获取请求数据
        data = request.get_json()
        if not data or 'username' not in data or 'password' not in data:
            return jsonify({'error': '用户名和密码必填'}), 400

        username = data['username']
        password = data['password']
        try:
            # 获取数据库连接
            conn = get_db_connection()
            cursor = conn.cursor()

            cursor.execute(
                "SELECT 用户ID,用户密码 FROM 用户表 WHERE 用户名称 = %s ",
                (username,)
            )
            user_data = cursor.fetchone()
            if not user_data:
                return jsonify({"message": "账号不存在，请联系客服处理"}), 400
            try:
                userID = user_data["用户ID"]
                db_password = user_data["用户密码"]
                if check_password_hash(db_password, password):
                    # 从数据库的点赞表中获取所有被用户点赞的文章的ID，并返回对应的文章内容
                    cursor.execute(
                        "SELECT 社区文章.* FROM 社区文章 INNER JOIN 点赞表 ON 社区文章.文章ID = 点赞表.点赞对象ID WHERE 点赞表.点赞者ID = %s Order by 点赞表.点赞时间 DESC",
                        (userID,)
                    )
                    articles_data = cursor.fetchall()
                    # 获取所有不重复的作者ID
                    author_ids = list({article['作者ID'] for article in articles_data})

                    # 查询这些作者的用户信息
                    author_infos = {}
                    if author_ids:
                        format_strings = ','.join(['%s'] * len(author_ids))
                        cursor.execute(
                            f"SELECT 用户ID, 用户名称, 用户等级, 用户头像 FROM 用户表 WHERE 用户ID IN ({format_strings})",
                            tuple(author_ids)
                        )
                        for author in cursor.fetchall():
                            author_infos[author['用户ID']] = {
                                'name': author['用户名称'],
                                'level': author['用户等级'],
                                'avatar_url': author['用户头像']
                            }

                        # 将文章数据转换为字典列表
                    articles = []
                    for article in articles_data:
                        author_id = article['作者ID']
                        author_info = author_infos.get(author_id, {
                            'name': '未知用户',
                            'level': 0,
                            'avatar_url': None
                        })
                        articles.append({
                            'article_id': article['文章ID'],
                            'title': article['文章标题'],
                            'content': article['文章内容'],
                            'create_time': article['发表时间'].strftime('%Y-%m-%d %H:%M:%S') if article[
                                '发表时间'] else None,
                            'author': {
                                'name': author_info['name'],
                                'level': author_info['level'],
                                'avatar_url': author_info['avatar_url']
                            },

                            'thumbs_up_numbers':article['点赞总数'],
                            'collect_numbers':article['收藏总数'],
                            'audio':article['音频'],
                            'image':article['图片']
                        })

                    return jsonify({'articles': articles}), 200
            except(ValueError, TypeError) as e:
                # 处理解包错误
                return jsonify({"message": "数据解析错误"}), 500
        except pymysql.Error as e:
            # 发生错误时回滚
            if 'conn' in locals():
                conn.rollback()
            return jsonify({"message": f"数据库错误: {str(e)}"}), 500

        finally:
            # 确保关闭游标和连接
            if 'cursor' in locals():
                cursor.close()
            if 'conn' in locals():
                conn.close()


    except Exception as e:
        print(f"Error: {e}")
        return jsonify({'error': '服务器错误'}), 500

@app.route('/get_star_article',methods=['POST'])
def get_star_article():
    try:
        # 获取请求数据
        data = request.get_json()
        if not data or 'username' not in data or 'password' not in data:
            return jsonify({'error': '用户名和密码必填'}), 400

        username = data['username']
        password = data['password']
        try:
            # 获取数据库连接
            conn = get_db_connection()
            cursor = conn.cursor()

            cursor.execute(
                "SELECT 用户ID,用户密码 FROM 用户表 WHERE 用户名称 = %s ",
                (username,)
            )
            user_data = cursor.fetchone()
            if not user_data:
                return jsonify({"message": "账号不存在，请联系客服处理"}), 400
            try:
                userID = user_data["用户ID"]
                db_password = user_data["用户密码"]
                if check_password_hash(db_password, password):
                    # 从数据库的点赞表中获取所有被用户点赞的文章的ID，并返回对应的文章内容
                    cursor.execute(
                        "SELECT 社区文章.* FROM 社区文章 INNER JOIN 收藏表 ON 社区文章.文章ID = 收藏表.收藏对象ID WHERE 收藏表.收藏者ID = %s Order by 收藏表.收藏时间 DESC",
                        (userID,)
                    )
                    articles_data = cursor.fetchall()
                    # 获取所有不重复的作者ID
                    author_ids = list({article['作者ID'] for article in articles_data})

                    # 查询这些作者的用户信息
                    author_infos = {}
                    if author_ids:
                        format_strings = ','.join(['%s'] * len(author_ids))
                        cursor.execute(
                            f"SELECT 用户ID, 用户名称, 用户等级, 用户头像 FROM 用户表 WHERE 用户ID IN ({format_strings})",
                            tuple(author_ids)
                        )
                        for author in cursor.fetchall():
                            author_infos[author['用户ID']] = {
                                'name': author['用户名称'],
                                'level': author['用户等级'],
                                'avatar_url': author['用户头像']
                            }

                        # 将文章数据转换为字典列表
                    articles = []
                    for article in articles_data:
                        author_id = article['作者ID']
                        author_info = author_infos.get(author_id, {
                            'name': '未知用户',
                            'level': 0,
                            'avatar_url': None
                        })
                        articles.append({
                            'article_id': article['文章ID'],
                            'title': article['文章标题'],
                            'content': article['文章内容'],
                            'create_time': article['发表时间'].strftime('%Y-%m-%d %H:%M:%S') if article[
                                '发表时间'] else None,
                            'author': {
                                'name': author_info['name'],
                                'level': author_info['level'],
                                'avatar_url': author_info['avatar_url']
                            },

                            'thumbs_up_numbers':article['点赞总数'],
                            'collect_numbers':article['收藏总数'],
                            'audio':article['音频'],
                            'image':article['图片']
                        })

                    return jsonify({'articles': articles}), 200
            except(ValueError, TypeError) as e:
                # 处理解包错误
                return jsonify({"message": "数据解析错误"}), 500
        except pymysql.Error as e:
            # 发生错误时回滚
            if 'conn' in locals():
                conn.rollback()
            return jsonify({"message": f"数据库错误: {str(e)}"}), 500

        finally:
            # 确保关闭游标和连接
            if 'cursor' in locals():
                cursor.close()
            if 'conn' in locals():
                conn.close()


    except Exception as e:
        print(f"Error: {e}")
        return jsonify({'error': '服务器错误'}), 500
@app.route('/get_my_article',methods=['POST'])
def get_my_article():
    try:
        # 获取请求数据
        data = request.get_json()
        if not data or 'username' not in data or 'password' not in data:
            return jsonify({'error': '用户名和密码必填'}), 400

        username = data['username']
        password = data['password']
        try:
            # 获取数据库连接
            conn = get_db_connection()
            cursor = conn.cursor()

            cursor.execute(
                "SELECT 用户ID,用户密码 FROM 用户表 WHERE 用户名称 = %s ",
                (username,)
            )
            user_data = cursor.fetchone()
            if not user_data:
                return jsonify({"message": "账号不存在，请联系客服处理"}), 400
            try:
                userID = user_data["用户ID"]
                db_password = user_data["用户密码"]
                if check_password_hash(db_password, password):
                    # 从数据库的点赞表中获取所有被用户点赞的文章的ID，并返回对应的文章内容
                    cursor.execute(
                        "SELECT 社区文章.* FROM 社区文章 INNER JOIN 用户表 ON 社区文章.作者ID = 用户表.用户ID WHERE 用户表.用户ID = %s Order by 社区文章.发表时间 DESC",
                        (userID,)
                    )
                    articles_data = cursor.fetchall()
                    # 获取所有不重复的作者ID
                    author_ids = list({article['作者ID'] for article in articles_data})

                    # 查询这些作者的用户信息
                    author_infos = {}
                    if author_ids:
                        format_strings = ','.join(['%s'] * len(author_ids))
                        cursor.execute(
                            f"SELECT 用户ID, 用户名称, 用户等级, 用户头像 FROM 用户表 WHERE 用户ID IN ({format_strings})",
                            tuple(author_ids)
                        )
                        for author in cursor.fetchall():
                            author_infos[author['用户ID']] = {
                                'name': author['用户名称'],
                                'level': author['用户等级'],
                                'avatar_url': author['用户头像']
                            }

                        # 将文章数据转换为字典列表
                    articles = []
                    for article in articles_data:
                        author_id = article['作者ID']
                        author_info = author_infos.get(author_id, {
                            'name': '未知用户',
                            'level': 0,
                            'avatar_url': None
                        })
                        articles.append({
                            'article_id': article['文章ID'],
                            'title': article['文章标题'],
                            'content': article['文章内容'],
                            'create_time': article['发表时间'].strftime('%Y-%m-%d %H:%M:%S') if article[
                                '发表时间'] else None,
                            'author': {
                                'name': author_info['name'],
                                'level': author_info['level'],
                                'avatar_url': author_info['avatar_url']
                            },

                            'thumbs_up_numbers':article['点赞总数'],
                            'collect_numbers':article['收藏总数'],
                            'audio':article['音频'],
                            'image':article['图片']
                        })

                    return jsonify({'articles': articles}), 200
            except(ValueError, TypeError) as e:
                # 处理解包错误
                return jsonify({"message": "数据解析错误"}), 500
        except pymysql.Error as e:
            # 发生错误时回滚
            if 'conn' in locals():
                conn.rollback()
            return jsonify({"message": f"数据库错误: {str(e)}"}), 500

        finally:
            # 确保关闭游标和连接
            if 'cursor' in locals():
                cursor.close()
            if 'conn' in locals():
                conn.close()


    except Exception as e:
        print(f"Error: {e}")
        return jsonify({'error': '服务器错误'}), 500

@app.route('/like_article',methods=['POST'])
def like_article():
    try:
        # 获取请求数据
        data = request.get_json()
        if not data or 'username' not in data or 'password' not in data:
            return jsonify({'error': '用户名和密码必填'}), 400

        username = data['username']
        password = data['password']
        article_ID= data['articleID']
        try:
            # 获取数据库连接
            conn = get_db_connection()
            cursor = conn.cursor()

            cursor.execute(
                "SELECT 用户ID,用户密码 FROM 用户表 WHERE 用户名称 = %s ",
                (username,)
            )
            user_data = cursor.fetchone()
            if not user_data:
                return jsonify({"message": "账号不存在，请联系客服处理"}), 400
            try:
                userID = user_data["用户ID"]
                db_password = user_data["用户密码"]
                if check_password_hash(db_password, password):
                    #从文章表中查找对应的文章，如果存在就在点赞表中添加记录，记录值为点赞人，点赞文章ID
                    # 检查文章是否存在
                    cursor.execute(
                        "SELECT 文章ID FROM 社区文章 WHERE 文章ID = %s",
                        (article_ID,)
                    )
                    article = cursor.fetchone()
                    if not article:
                        return jsonify({"message": "文章不存在"}), 404

                    # 检查是否已经点赞过
                    cursor.execute(
                        "SELECT 点赞者ID FROM 点赞表 WHERE 点赞者ID = %s AND 点赞对象ID = %s",
                        (userID, article_ID)
                    )
                    existing_like = cursor.fetchone()
                    if existing_like:
                        return jsonify({"message": "您已经点赞过该文章"}), 400

                    # 添加点赞记录
                    cursor.execute(
                        "INSERT INTO 点赞表 (点赞者ID, 点赞对象ID) VALUES (%s, %s)",
                        (userID, article_ID)
                    )

                    # 更新文章的点赞数
                    cursor.execute(
                        "UPDATE 社区文章 SET 点赞总数 = 点赞总数 + 1 WHERE 文章ID = %s",
                        (article_ID,)
                    )
                    # 更新被点赞者的被点赞数
                    cursor.execute(
                        "UPDATE 用户表 SET 被点赞数 = 被点赞数 + 1 WHERE 用户ID = %s",
                        (userID,)
                    )
                    conn.commit()


                    return jsonify({'message': '点赞成功'}), 200
            except(ValueError, TypeError) as e:
                # 处理解包错误
                return jsonify({"message": "数据解析错误"}), 500
        except pymysql.Error as e:
            # 发生错误时回滚
            if 'conn' in locals():
                conn.rollback()
            return jsonify({"message": f"数据库错误: {str(e)}"}), 500

        finally:
            # 确保关闭游标和连接
            if 'cursor' in locals():
                cursor.close()
            if 'conn' in locals():
                conn.close()


    except Exception as e:
        print(f"Error: {e}")
        return jsonify({'error': '服务器错误'}), 500

@app.route('/start_article',methods=['POST'])
def start_article():
    try:
        # 获取请求数据
        data = request.get_json()
        if not data or 'username' not in data or 'password' not in data:
            return jsonify({'error': '用户名和密码必填'}), 400

        username = data['username']
        password = data['password']
        article_ID= data['articleID']
        try:
            # 获取数据库连接
            conn = get_db_connection()
            cursor = conn.cursor()

            cursor.execute(
                "SELECT 用户ID,用户密码 FROM 用户表 WHERE 用户名称 = %s ",
                (username,)
            )
            user_data = cursor.fetchone()
            if not user_data:
                return jsonify({"message": "账号不存在，请联系客服处理"}), 400
            try:
                userID = user_data["用户ID"]
                db_password = user_data["用户密码"]
                if check_password_hash(db_password, password):
                    #从文章表中查找对应的文章，如果存在就在点赞表中添加记录，记录值为点赞人，点赞文章ID
                    # 检查文章是否存在
                    cursor.execute(
                        "SELECT 文章ID FROM 社区文章 WHERE 文章ID = %s",
                        (article_ID,)
                    )
                    article = cursor.fetchone()
                    if not article:
                        return jsonify({"message": "文章不存在"}), 404

                    # 检查是否已经点赞过
                    cursor.execute(
                        "SELECT 收藏者ID FROM 收藏表 WHERE 收藏者ID = %s AND 收藏对象ID = %s",
                        (userID, article_ID)
                    )
                    existing_like = cursor.fetchone()
                    if existing_like:
                        return jsonify({"message": "您已经点赞过该文章"}), 400

                    # 添加点赞记录
                    cursor.execute(
                        "INSERT INTO 收藏表 (收藏者ID, 收藏对象ID) VALUES (%s, %s)",
                        (userID, article_ID)
                    )

                    # 更新文章的点赞数
                    cursor.execute(
                        "UPDATE 社区文章 SET 收藏总数 = 收藏总数 + 1 WHERE 文章ID = %s",
                        (article_ID,)
                    )
                    conn.commit()


                    return jsonify({'message': '点赞成功'}), 200
            except(ValueError, TypeError) as e:
                # 处理解包错误
                return jsonify({"message": "数据解析错误"}), 500
        except pymysql.Error as e:
            # 发生错误时回滚
            if 'conn' in locals():
                conn.rollback()
            return jsonify({"message": f"数据库错误: {str(e)}"}), 500

        finally:
            # 确保关闭游标和连接
            if 'cursor' in locals():
                cursor.close()
            if 'conn' in locals():
                conn.close()


    except Exception as e:
        print(f"Error: {e}")
        return jsonify({'error': '服务器错误'}), 500


@app.route('/get_image/<filename>')
def get_image(filename):
    file_path = f"uploads/images/{filename}"

    # 移除 as_attachment=True 以直接在浏览器中显示图片
    return send_file(
        file_path,
        download_name=filename,
        mimetype='application/octet-stream'
    )

@app.route('/get_audio/<filename>')
def get_audio(filename):
    file_path = f"uploads/audio/{filename}"

    # 移除 as_attachment=True 以直接在浏览器中显示图片
    return send_file(
        file_path,
        download_name=filename,
        mimetype='application/octet-stream'
    )

@app.route('/get_profile_photo/<filename>')
def get_profile_photo(filename):
    file_path = f"uploads/profiles/{filename}"

    # 移除 as_attachment=True 以直接在浏览器中显示图片
    return send_file(
        file_path,
        download_name=filename,
        mimetype='application/octet-stream'
    )


@app.route('/reset_username',methods=['POST'])
def reset_username():
    try:
        # 获取请求数据
        data = request.get_json()
        if not data or 'username' not in data or 'password' not in data:
            return jsonify({'error': '用户名和密码必填'}), 400

        username = data['username']
        password = data['password']
        reset_username=data['reset_username']
        try:
            # 获取数据库连接
            conn = get_db_connection()
            cursor = conn.cursor()

            cursor.execute(
                "SELECT 用户ID,用户密码 FROM 用户表 WHERE 用户名称 = %s ",
                (username,)
            )
            user_data = cursor.fetchone()
            if not user_data:
                return jsonify({"message": "账号不存在，请联系客服处理"}), 400
            try:
                userID = user_data["用户ID"]
                db_password = user_data["用户密码"]
                if check_password_hash(db_password, password):
                    # 检查新用户名是否已存在
                    cursor.execute(
                        "SELECT 用户ID FROM 用户表 WHERE 用户名称 = %s",
                        (reset_username,)
                    )
                    if cursor.fetchone():
                        return jsonify({"message": "该用户名已被使用，请选择其他用户名"}), 400

                    # 更新用户名
                    cursor.execute(
                        "UPDATE 用户表 SET 用户名称 = %s WHERE 用户ID = %s",
                        (reset_username, userID)
                    )
                    conn.commit()
                    return jsonify({'message': '修改用户名成功'}), 200
                else:
                    return jsonify({"message": "密码错误"}), 401
            except(ValueError, TypeError) as e:
                # 处理解包错误
                return jsonify({"message": "数据解析错误"}), 500
        except pymysql.Error as e:
            # 发生错误时回滚
            if 'conn' in locals():
                conn.rollback()
            return jsonify({"message": f"数据库错误: {str(e)}"}), 500

        finally:
            # 确保关闭游标和连接
            if 'cursor' in locals():
                cursor.close()
            if 'conn' in locals():
                conn.close()


    except Exception as e:
        print(f"Error: {e}")
        return jsonify({'error': '服务器错误'}), 500





if __name__ == '__main__':
    app.run(debug=True, host='0.0.0.0', port=5000)





